package nbpMaven2.nbpMaven2;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class DBConnectionClass implements IDBConnecting {

	// tworzy polaczenie dla bazy

	public Connection createConnection() throws ClassNotFoundException, SQLException {
		Connection conn = null;
		Class.forName("org.postgresql.Driver");
		String url = "jdbc:postgresql://localhost:5433/danonDB";
		conn = DriverManager.getConnection(url, "postgres", "danone");

		return conn;
	}

	// /hsgahjdasbvbdvhas
	// pobiera id waluty

	public int getCurrId(String currName) throws SQLException, ClassNotFoundException {
		int id = 0;
		Connection conn = createConnection();
		Statement state = conn.createStatement();
		ResultSet res = state.executeQuery("select id as id from currency where shortcut='" + currName + "'");
		while (res.next()) {
			id = res.getInt("id");
		}
		res.close();
		state.close();
		conn.close();
		return id;
	}

	// wpisuje walute i skrot do bazy

	public void setCurr(String currName, String currShort) {
		try {
			Connection conn = createConnection();
			Statement state = conn.createStatement();
			state.executeUpdate("insert into currency(name, shortcut) values('" + currName + "', '" + currShort + "')");
			state.close();
			conn.close();
		} catch (ClassNotFoundException ex) {
			throw new ClassIsNotExist("This class is not exists!");
		}

		catch (SQLException ex) {
			throw new SqlExcept("There is some SQL Exception!");
		}
	}

	// dopisuje do bazy kurs waluty w danym dniu

	public void setCurrValue(Date date, BigDecimal bd, int idcurr) throws ClassNotFoundException, SQLException {
		Connection conn = createConnection();
		Statement state = conn.createStatement();
		state.executeUpdate("insert into avgrate(date, value, idcurr) values('" + date + "', " + bd + ", " + idcurr + ")");
		state.close();
		conn.close();
	}

	// sprawdza czy w bazie jest juz taka waluta w danym dniu

	public boolean checkCurrInAvgTab(Date date, int id) throws ClassNotFoundException, SQLException {
		int value = 0;
		Connection conn = createConnection();
		Statement state = conn.createStatement();
		ResultSet res = state.executeQuery("select count(*) from avgrate where date='" + date + "' and idcurr=" + id + "");
		while (res.next()) {
			value = res.getInt("count");
		}
		boolean check = value != 0 ? true : false;
		res.close();
		state.close();
		conn.close();

		return check;
	}

	public boolean checkDateInAvgTab(Date date) throws ClassNotFoundException, SQLException {
		int value = 0;
		Connection conn = createConnection();
		Statement state = conn.createStatement();
		ResultSet res = state.executeQuery("select count(distinct date) from avgrate where date='" + date + "'");
		while (res.next()) {
			value = res.getInt("count");
		}
		boolean check = value == 1 ? true : false;

		res.close();
		state.close();
		conn.close();

		return check;
	}

	public String getBiggestCurrencyNameDifference() throws ClassNotFoundException, SQLException {
		int idcurr = 0;
		String currName = "";
		Connection conn = createConnection();
		Statement state = conn.createStatement();
		ResultSet res = state.executeQuery("select idcurr from (select idcurr, (max-min) as value from (select idcurr, min(value), max(value) from avgrate where idcurr in (select distinct idcurr from avgrate) group by idcurr) as tab order by value desc limit 1) as final");
		while (res.next()) {
			idcurr = res.getInt("idcurr");
		}
		res = state.executeQuery("select name from currency where id=" + idcurr + "");
		while (res.next()) {
			currName = res.getString("name");
		}

		res.close();
		state.close();
		conn.close();

		return currName;
	}

	public void setCurrencyToCountry(String currShort, String country) throws CountryHasThisCurrency, ThisShortcutIsIncorrect {
		try {
			// przypisanie waluty do kraju
			if (currShort.length() == 3) {
				int rowValue = 0;
				Connection conn = createConnection();
				Statement state = conn.createStatement();
				ResultSet res = state.executeQuery("select count(*) from country where shortcut_fk='" + currShort.toUpperCase() + "' and countryname='" + country.toLowerCase() + "'");
				while (res.next()) {
					rowValue = res.getInt("count");
				}
				if (rowValue == 0) {
					state.executeUpdate("insert into country(countryname, shortcut_fk) values('" + country.toLowerCase() + "', '" + currShort.toUpperCase() + "')");
					System.out.println("Currency succesfully added to county :)");
				}

				else
					throw new CountryHasThisCurrency("This country has this currency!");

				res.close();
				state.close();
				conn.close();
			} else
				throw new ThisShortcutIsIncorrect("This shortcut is incorrect!");
		}

		catch (ClassNotFoundException ex) {
			throw new ClassIsNotExist("This class is not exists!");
		}

		catch (SQLException ex) {
			throw new SqlExcept("There is some SQL Exception!");
		}
	}

	public CurrencyInfoClass getCurrencyShortcutAndVal(Date date, String type) throws ClassNotFoundException, SQLException {
		CurrencyInfoClass cic = new CurrencyInfoClass();
		Connection conn = createConnection();
		Statement state = conn.createStatement();
		ResultSet res = state.executeQuery("select " + type.toLowerCase() + "(value) from avgrate where date='" + date + "'");
		while (res.next()) {
			cic.setBd(res.getBigDecimal(type.toLowerCase()));
		}
		res = state.executeQuery("select currency.shortcut from avgrate join currency on currency.id=avgrate.idcurr where value=" + cic.getBd() + " and date='" + date + "'");
		while (res.next()) {
			cic.setShortcut(res.getString("shortcut"));
		}

		res.close();
		state.close();
		conn.close();

		return cic;
	}
}
